package it.unica.informatica.enotes.database;

import android.app.SearchManager;
import android.content.ContentValues;
import android.content.Context;
import android.content.res.Resources;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Calendar;

import it.unica.informatica.enotes.R;

/**
 * This class contains all the operations necessary to crate Database
 *
 * @author Massimiliano Meles
 * @version 1.0
 */
public class DatabaseHelper extends SQLiteOpenHelper {

    /* ********************************************************************************************
     *                                    VARIABLES AND CONSTANTS                                 *
     **********************************************************************************************/

   private static final String DATABASE_NAME = "noteDatabase";
   private static final int DATABASE_VERSION = 1;

   public static final String KEY_TAG = SearchManager.SUGGEST_COLUMN_TEXT_1;

   /*Definition of query to create table in the Database */
   private static final String FTS_VIRTUAL_TABLE = "FTStags";

   private static final String NOTE_TABLE_CREATE = "create table note (id integer primary key, title text not null,  last_change text not null, extra_data longtext not null);";

   private static final String TAG_TABLE_CREATE = "create table tag  (id integer primary key, tag_name text not null);";

   private static final String TAGS_NOTE_TABLE_CREATE = "create table tags_notes (" +
           "id_note integer REFERENCES note(id) ON UPDATE CASCADE ON DELETE CASCADE, " +
           "id_tag integer REFERENCES tag(id) ON UPDATE CASCADE ON DELETE CASCADE," +
           "PRIMARY KEY(id_note, id_tag));";


   private static final String FTS_TABLE_CREATE = "CREATE VIRTUAL TABLE " + FTS_VIRTUAL_TABLE + " USING fts3 (" + KEY_TAG + ");";

    /* ********************************************************************************************
     *                     METHODS FOR CREATION AND UPDATING OF THE DATABASE                      *
     **********************************************************************************************/

   /**
    * Create a DatabaseHelper
    *
    * @param context Context of the application
    */
   public DatabaseHelper(Context context) {
      super(context, DATABASE_NAME, null, DATABASE_VERSION);
   }

   /*Called during creation of the Database */
   @Override
   public void onCreate(SQLiteDatabase database) {
      database.execSQL(NOTE_TABLE_CREATE);
      database.execSQL(TAG_TABLE_CREATE);
      database.execSQL(TAGS_NOTE_TABLE_CREATE);
      database.execSQL(FTS_TABLE_CREATE);
   }

   // Called when need a update of the database (E.g. when version of Database is changed)
   @Override
   public void onUpgrade(SQLiteDatabase database, int oldVersion, int newVersion) {

      database.execSQL("DROP TABLE IF EXISTS note");
      database.execSQL("DROP TABLE IF EXISTS tag");
      database.execSQL("DROP TABLE IF EXISTS tags_note");
      database.execSQL("DROP TABLE IF EXISTS " + FTS_VIRTUAL_TABLE);
      onCreate(database);

   }


}
